'''
Created on 2011-5-23

@author: elm
'''
from ddbms.Schema import *
from ddbms.SqlStatement import *
def StaticSchema():
    '''
    This database schema is hard coded 
    and is used for test purpose only
    '''
    
    
    
    
    schema = Schema()
    schema.TableId = 1
    schema.TableColumnId = 1
    schema.SegmentId = 1
    
    def gen():
        s = Site()
        s.id = 1
        s.name = 'A'
        s.connection = "localhost"
        schema.siteId = 1
        Publisher()
        Customer()
        Book()
        Order()
                
        s.segments = []
        for seg in schema.listSegments():
            s.segments.append(seg.id)
        schema.sites[1]=s
        
    
    def genColumn(table,name,type,length):
        column = Column()
        column.id = schema.TableColumnId
        column.table = table.id
        schema.TableColumnId += 1
        column.name = name
        column.type = type
        column.length = length
        schema.columns[column.id]=column
        return column
    
    def genHorizontalSegment(table,name,cond):
        seg = HorizontalSegment()
        seg.id = schema.SegmentId;
        seg.table = table.id
        schema.SegmentId += 1;        
        seg.columns = table.columns        
        seg.segmentCondition = cond
        seg.sites = [schema.siteId]
        seg.name = name
        schema.segments[seg.id]=seg
        return seg
    
    def genVerticalSegment(table,name,columns):
        seg = VerticalSegment()
        seg.id = schema.SegmentId
        seg.table = table.id
        schema.SegmentId += 1;
        seg.sites = [schema.siteId]
        seg.name = name
        cols = []
        for cname in columns:
            cols += [schema.findColumn(table,cname).id]
        seg.columns = cols
        
        schema.segments[seg.id]=seg
        return seg

    def Publisher():
        table = Table()        
        table.name = 'publisher'
        table.id = schema.TableId    
        schema.TableId += 1
        schema.tables[table.id] = table
        
        table.primaryKey="id"
        table.columns = [genColumn(table,'id','int',10).id]
        table.columns += [genColumn(table,'name','varchar',100).id]
        table.columns += [genColumn(table,'nation','varchar',3).id]    
        
        
        table.segmentation = Segment.Horizontal
        seg = genHorizontalSegment(table,"p1",LogicalAnd(
                TestValue(schema.toFieldRef(table,'id'),Ops.LT,10400),
                TestValue(schema.toFieldRef(table,'nation'),Ops.EQ,'prc')))
        table.segments = [seg.id]
        seg.sibling = schema.SegmentId;        
        
        seg = genHorizontalSegment(table,"p2",LogicalAnd(
                TestValue(schema.toFieldRef(table,'id'),Ops.LT,10400),
                TestValue(schema.toFieldRef(table,'nation'),Ops.EQ,'usa')))
        table.segments += [seg.id]
        seg.sibling = schema.SegmentId;
        
        seg = genHorizontalSegment(table,"p3",LogicalAnd(
                TestValue(schema.toFieldRef(table,'id'),Ops.GTEQ,10400),
                TestValue(schema.toFieldRef(table,'nation'),Ops.EQ,'prc')))
        table.segments += [seg.id]
        seg.sibling = schema.SegmentId;
        seg = genHorizontalSegment(table,"p4",LogicalAnd(
                TestValue(schema.toFieldRef(table,'id'),Ops.GTEQ,10400),
                TestValue(schema.toFieldRef(table,'nation'),Ops.EQ,'usa')))
        table.segments += [seg.id]
        
        return table
    
    def Customer():
        table = Table()        
        table.name = 'customer'
        table.id = schema.TableId
        schema.TableId += 1
        schema.tables[table.id] = table
        
        table.primaryKey="id"
        table.columns = [genColumn(table,'id','int',10).id]
        table.columns += [genColumn(table,'name','varchar',25).id]
        table.columns += [genColumn(table,'rank','int',10).id]    
        
        table.segmentation = Segment.Vertical
        seg = genVerticalSegment(table,"c1",['id','name'])
        table.segments = [seg.id]
        seg.sibling = schema.SegmentId;        
        
        seg = genVerticalSegment(table,"c2",['id','rank'])
        table.segments += [seg.id]        
        
        return table
    
    def Book():
        table = Table()        
        table.name = 'book'
        table.id = schema.TableId
        schema.TableId += 1
        schema.tables[table.id] = table
        
        table.primaryKey="id"
        table.columns = [genColumn(table,'id','int',10).id]
        table.columns += [genColumn(table,'title','varchar',100).id]
        table.columns += [genColumn(table,'authors','varchar',25).id]
        table.columns += [genColumn(table,'publisher_id','int',10).id]
        table.columns += [genColumn(table,'copies','int',10).id]
        
        table.segmentation = Segment.Horizontal
        seg = genHorizontalSegment(table,"b1",
                TestValue(schema.toFieldRef(table,'id'),Ops.LT,20500))
        table.segments = [seg.id]
        seg.sibling = schema.SegmentId;        
        
        seg = genHorizontalSegment(table,"b2",LogicalAnd(
                TestValue(schema.toFieldRef(table,'id'),Ops.GTEQ,20500),
                TestValue(schema.toFieldRef(table,'id'),Ops.LT,  21000)))
        table.segments += [seg.id]
        seg.sibling = schema.SegmentId;
        
        seg = genHorizontalSegment(table,"b3",
                TestValue(schema.toFieldRef(table,'id'),Ops.GTEQ,21000))
        table.segments += [seg.id]                 
        
        return table
    
    def Order():
        table = Table()        
        table.name = 'orders'
        table.id = schema.TableId
        schema.TableId += 1
        schema.tables[table.id] = table
        
        table.primaryKey=None
        table.columns = [genColumn(table,'customer_id','int',10).id]
        table.columns += [genColumn(table,'book_id','int',10).id]
        table.columns += [genColumn(table,'quantity','int',10).id]
        
        table.segmentation = Segment.Horizontal
        seg = genHorizontalSegment(table,"o1",LogicalAnd(
                TestValue(schema.toFieldRef(table,'customer_id'),Ops.LT,30700),
                TestValue(schema.toFieldRef(table,'book_id'),Ops.LT,  21500)))
        table.segments = [seg.id]
        seg.sibling = schema.SegmentId;        
        
        seg = genHorizontalSegment(table,"o2",LogicalAnd(
                TestValue(schema.toFieldRef(table,'customer_id'),Ops.LT,30700),
                TestValue(schema.toFieldRef(table,'book_id'),Ops.GTEQ,  21500)))
        table.segments += [seg.id]
        seg.sibling = schema.SegmentId;  
        
        seg = genHorizontalSegment(table,"o3",LogicalAnd(
                TestValue(schema.toFieldRef(table,'customer_id'),Ops.GTEQ,30700),
                TestValue(schema.toFieldRef(table,'book_id'),Ops.LT,  21500)))
        table.segments += [seg.id]
        seg.sibling = schema.SegmentId; 
        
        seg = genHorizontalSegment(table,"o4",LogicalAnd(
                TestValue(schema.toFieldRef(table,'customer_id'),Ops.GTEQ,30700),
                TestValue(schema.toFieldRef(table,'book_id'),Ops.GTEQ,  21500)))
        table.segments += [seg.id]                       
        
        return table
    
    gen()
    return schema
    